﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace Skywave.Data
{
    public enum DbCommandHelperAdditionalCommands
    {
        None,
        Count,
        Sum,
        Average
    }
    public abstract class DbCommandHelper
    {
        public abstract string FixCommandString(string sqlQ);

        public virtual void FixCommand(DbCommand dbCommand1)
        {
            string sqlQ = dbCommand1.CommandText;
            sqlQ = FixCommandString(sqlQ);
            dbCommand1.CommandText = sqlQ;
        }

        public SqlCommandString GetSqlCommandString(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs)
        {
            return GetSqlCommandString(dc1, qInput1, additionalCommand, additionalCommandArgs, true);
        }
        public SqlCommandString GetSqlCommandString(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs, bool fixCommandString)
        {
            DbCommand dbCommand1 = GetDbCommand(dc1, qInput1, additionalCommand, additionalCommandArgs, fixCommandString);
            SqlCommandString r = new SqlCommandString();
            r.Params = new List<string>();
            r.CommandString = dbCommand1.CommandText;
            string paramType, paramValue;
            foreach (DbParameter fe1 in dbCommand1.Parameters)
            {
                int fe1_Size = (fe1.Size <= 0) ? 1 : fe1.Size;
                switch (fe1.DbType)
                {
                    case System.Data.DbType.AnsiString:
                        paramType = string.Format("VARCHAR({0})", fe1_Size);
                        paramValue = string.Format("'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.AnsiStringFixedLength:
                        paramType = string.Format("CHAR({0})", fe1_Size);
                        paramValue = string.Format("'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.Binary:
                        paramType = string.Format("BINARY", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Boolean:
                        paramType = string.Format("BIT", fe1_Size);
                        if (fe1.Value is bool)
                            paramValue = ((bool)fe1.Value) ? "1" : "0";
                        else
                            paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Byte:
                        paramType = string.Format("TINYINT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Time:
                    case System.Data.DbType.Date:
                    case System.Data.DbType.DateTime:
                    case System.Data.DbType.DateTime2:
                    case System.Data.DbType.DateTimeOffset:
                        paramType = "DATETIME";
                        if (fe1.Value is DateTime)
                            paramValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-ddTHH:mm:ss.fff"));
                        else
                            paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Decimal:
                        paramType = string.Format("DECIMAL", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Double:
                        paramType = string.Format("FLOAT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Int16:
                        paramType = string.Format("SMALLINT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Int32:
                        paramType = string.Format("INT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Int64:
                        paramType = string.Format("BIGINT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.SByte:
                        paramType = string.Format("TINYINT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Single:
                        paramType = string.Format("REAL", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.String:
                        paramType = string.Format("NVARCHAR({0})", fe1_Size);
                        paramValue = string.Format("N'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.StringFixedLength:
                        paramType = string.Format("NCHAR({0})", fe1_Size);
                        paramValue = string.Format("N'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.UInt16:
                        paramType = string.Format("INT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.UInt32:
                        paramType = string.Format("BIGINT", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.UInt64:
                        paramType = string.Format("NUMERIC(8,0)", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.VarNumeric:
                        paramType = string.Format("NUMERIC", fe1_Size);
                        paramValue = string.Format("{0}", fe1.Value);
                        break;
                    case System.Data.DbType.Xml:
                    case System.Data.DbType.Object:
                    case System.Data.DbType.Guid:
                    case System.Data.DbType.Currency:
                    default:
                        paramType = "";
                        paramValue = "";
                        break;
                }
                //
                if (fe1.Value == null)
                    paramValue = "NULL";
                //
                if (!string.IsNullOrEmpty(paramType))
                    r.Params.Add(string.Format("{0} {1} = {2}", fe1.ParameterName, paramType, paramValue));
            }
            return r;
        }

        public DbCommand GetDbCommand(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs)
        {
            return GetDbCommand(dc1, qInput1, additionalCommand, additionalCommandArgs, true);
        }
        public DbCommand GetDbCommand(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs, bool fixCommandString)
        {
            DbCommand dbCommand1 = dc1.GetCommand(qInput1);
            if (fixCommandString)
                FixCommand(dbCommand1);

            string str1;
            string sqlQ = dbCommand1.CommandText;
            switch (additionalCommand)
            {
                case DbCommandHelperAdditionalCommands.Count:
                    str1 = "*";
                    if (additionalCommandArgs is string && !string.IsNullOrEmpty((string)additionalCommandArgs))
                        str1 = (string)additionalCommandArgs;
                    sqlQ = string.Format("SELECT COUNT({0}) FROM ({1}) AS maT1", str1, sqlQ);
                    break;
                case DbCommandHelperAdditionalCommands.Sum:
                    str1 = "value";
                    if (additionalCommandArgs is string && !string.IsNullOrEmpty((string)additionalCommandArgs))
                        str1 = (string)additionalCommandArgs;
                    sqlQ = string.Format("SELECT SUM({0}) FROM ({1}) AS maT1", str1, sqlQ);
                    break;
                case DbCommandHelperAdditionalCommands.Average:
                    break;
                case DbCommandHelperAdditionalCommands.None:
                default:
                    break;
            }
            dbCommand1.CommandText = sqlQ;

            return dbCommand1;
        }

        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, DbCommand usedParametersContainer)
        { ChangeParametersNames(toChange, new List<DbCommand>() { usedParametersContainer }); }
        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, List<DbCommand> usedParametersContainers)
        {
            List<string> usedParameterNames = new List<string>();
            foreach (DbCommand fe1 in usedParametersContainers)
            {
                usedParameterNames = usedParameterNames.Union((from x1 in fe1.Parameters.Cast<DbParameter>()
                                                               select x1.ParameterName)).ToList();
            }
            ChangeParametersNames(toChange, usedParameterNames);
        }
        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, List<string> usedParameterNames)
        {
            int i = 1;
            string oldParam;
            string newParam;
            Dictionary<string, string> replacements = new Dictionary<string, string>();
            int j = 1;
            foreach (System.Data.Common.DbParameter fe1 in toChange.Parameters)
            {
                oldParam = fe1.ParameterName;
                newParam = oldParam;
                while (usedParameterNames.Contains(newParam))
                {
                    newParam = string.Format("@p{0}", i);
                    i++;
                }
                usedParameterNames.Add(newParam);
                fe1.ParameterName = newParam;
                toChange.CommandText = toChange.CommandText.Replace(oldParam, string.Format("$maP{0}$", j));
                replacements.Add(string.Format("$maP{0}$", j), newParam);
                j++;
            }
            foreach (string fe1 in replacements.Keys)
            {
                toChange.CommandText = toChange.CommandText.Replace(fe1, replacements[fe1]);
            }
        }

        public object[] GetParametersArray(DbCommand paramContainer)
        {
            return GetParametersArray(new List<DbCommand>() { paramContainer });
        }
        public object[] GetParametersArray(List<DbCommand> paramContainers)
        {
            System.Collections.ArrayList r = new System.Collections.ArrayList();
            foreach (DbCommand fe1 in paramContainers)
            {
                r.AddRange((from x1 in fe1.Parameters.Cast<DbParameter>()
                            select x1.Value).ToList());
            }
            return r.ToArray();
        }

        public static string GetSqlEmbededWithParams(DbCommand dbc1)
        {
            string r = "";
            //SQL Parameteres
            string sqlTextParams = "";
            foreach (DbParameter fe1 in dbc1.Parameters)
            {
                int fe1_Size = (fe1.Size <= 0) ? 1 : fe1.Size;
                string strType = "";
                string strValue = "";
                switch (fe1.DbType)
                {
                    case System.Data.DbType.AnsiString:
                        strType = string.Format("varchar({0})", fe1_Size);
                        strValue = string.Format("'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.AnsiStringFixedLength:
                        strType = string.Format("char({0})", fe1_Size);
                        strValue = string.Format("'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.Binary:
                        break;
                    case System.Data.DbType.Boolean:
                        strType = "bit";
                        strValue = (((bool)fe1.Value) ? 1 : 0).ToString();
                        break;
                    case System.Data.DbType.Byte:
                        break;
                    case System.Data.DbType.Currency:
                        break;
                    case System.Data.DbType.Date:
                        strType = "date";
                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-dd"));
                        break;
                    case System.Data.DbType.DateTime:
                    case System.Data.DbType.DateTime2:
                        strType = "datetime";
                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-ddTHH:mm:ss.fff"));
                        break;
                    case System.Data.DbType.DateTimeOffset:
                        break;
                    case System.Data.DbType.Decimal:
                        break;
                    case System.Data.DbType.Double:
                        strType = "float";
                        strValue = fe1.Value.ToString();
                        break;
                    case System.Data.DbType.Guid:
                        break;
                    case System.Data.DbType.Int16:
                        break;
                    case System.Data.DbType.Int32:
                        strType = "int";
                        strValue = fe1.Value.ToString();
                        break;
                    case System.Data.DbType.Int64:
                        break;
                    case System.Data.DbType.Object:
                        break;
                    case System.Data.DbType.SByte:
                        break;
                    case System.Data.DbType.Single:
                        break;
                    case System.Data.DbType.String:
                        strType = string.Format("nvarchar({0})", fe1_Size);
                        strValue = string.Format("N'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.StringFixedLength:
                        strType = string.Format("nchar({0})", fe1_Size);
                        strValue = string.Format("N'{0}'", fe1.Value);
                        break;
                    case System.Data.DbType.Time:
                        strType = "time";
                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("HH:mm:ss.fff"));
                        break;
                    case System.Data.DbType.UInt16:
                        break;
                    case System.Data.DbType.UInt32:
                        break;
                    case System.Data.DbType.UInt64:
                        break;
                    case System.Data.DbType.VarNumeric:
                        break;
                    case System.Data.DbType.Xml:
                        break;
                    default:
                        break;
                }
                if (string.IsNullOrEmpty(strValue))
                    strValue = "NULL";
                if (!string.IsNullOrEmpty(strType))
                    sqlTextParams += string.Format(",{0} {1}={2}\n\t", fe1.ParameterName, strType, strValue);
            }
            sqlTextParams = sqlTextParams.Trim(new char[] { ',', '\t' });
            sqlTextParams = string.Format("DECLARE \n\t{0};", sqlTextParams);
            r = sqlTextParams + "\n" + dbc1.CommandText;
            return r;
        }
    }
}
